﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Model.EnumUtil;
using Estsh.Core.Model.Extend.Models;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using System.Data;
using System.Text;

namespace Estsh.Core.Wms.Repositories
{
    /// <summary>
    /// 推荐入库
    /// </summary>
    public class RecommendedInStockRepository : BaseRepository<BaseEntity>, IRecommendedInStockRepository
    {
        public RecommendedInStockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }
        //根据条码获取条码数据
        public List<SysStock> GetStockByCarton(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.*,b.enum_name as stock_status FROM sys_stock a (nolock) left join sys_enum b (nolock) on a.status=b.enum_value and b.enum_type = 'sys_stock_status' WHERE carton_no='" + cartonNo + "'");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }
        //根据供应商编号获取库区零件号
        public List<SysZonePart> GetZonePartByVendorId(int VendorId)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_zone_part (nolock) WHERE vendor_id = '" + VendorId + "' and enabled='Y'  ORDER BY priority ");
                List<SysZonePart> sysZoneParts = dbConn.Query<SysZonePart>(stringBuilder.ToString()).ToList();
                return sysZoneParts;
            }
        }
        //根据分组号分组获取所有
        public List<SysStock> GetStockByLocate(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT SUM(qty) as qty FROM dbo.sys_stock (nolock) WHERE locate_name = '" + locateName + "' AND status IN ('50','60','80') and enabled='Y' ");
                List<SysStock> sysstockByGroupNoCount = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysstockByGroupNoCount;
            }
        }
        //根据分组号分组获取所有零件号
        public List<SysStock> GetStockPartByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.part_no FROM dbo.sys_stock a (nolock) WHERE group_no = '" + groupNo + "'  and status=40 and a.enabled='Y'  GROUP BY a.part_no ");
                List<SysStock> sysstockByGroupNoCount = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysstockByGroupNoCount;
            }
        }
        //根据零件号获取库区零件号信息，用于推荐库位
        public List<SysZonePart> GetZonePartByPartNo(string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_zone_part (nolock) WHERE part_no = '" + partNo + "' and enabled='Y' ORDER BY priority ");
                List<SysZonePart> sysZoneParts = dbConn.Query<SysZonePart>(stringBuilder.ToString()).ToList();
                return sysZoneParts;
            }
        }
        //根据库区查询库区下的库位

        public List<SysLocate> GetLocateByZoneName(string zoneName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_locate a (nolock) LEFT JOIN sys_locate_part b (nolock) ON a.locate_id = b.locate_id  WHERE zone_name = '" + zoneName + "'  and a.enabled='Y'");
                List<SysLocate> sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString()).ToList();
                return sysLocates;
            }
        }

        public List<SysLocate> GetLocateByZoneNameAndPartNo(string zoneName, string partNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_locate a (nolock) LEFT JOIN sys_locate_part b (nolock) ON a.locate_id = b.locate_id  WHERE zone_name = '" + zoneName + "' and b.part_no='"+ partNo + "' and a.enabled='Y'");
                List<SysLocate> sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString()).ToList();
                return sysLocates;
            }
        }

        //根据库位获取所有在库的数量和
        public SysStock GetStockByLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("  SELECT SUM(qty) as qty FROM dbo.sys_stock (nolock) WHERE locate_name = '" + locateName + "' AND status IN ('50','60','80') and enabled='Y' ");
                SysStock sysStocksQtySum = dbConn.QueryFirstOrDefault<SysStock>(stringBuilder.ToString());//库位在库数
                return sysStocksQtySum;
            }
        }
        //根据库位获取零件号不为空的条码信息
        public List<SysStock> GetStockByPartNoLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_stock (nolock) WHERE part_no!='' and locate_name = '" + locateName + "' and enabled='Y'");
                List<SysStock> sysStocks1 = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks1;
            }
        }
        //根据库位名称获取库位信息
        public List<SysLocate> GetLocateInfoByLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT a.locate_id,a.locate_name,a.locate_type,a.locate_capacity,b.enum_desc as locate_typeDesc,a.enabled,a.zone_id,a.zone_name,a.warehouse_id,a.warehouse_name,c.part_no  FROM dbo.sys_locate a (nolock) LEFT JOIN sys_enum b (nolock) ON b.enum_type='sys_locate_type' and a.locate_type=b.enum_value "
                                             + "  LEFT JOIN dbo.sys_locate_part c (nolock) ON a.locate_id=c.locate_id  WHERE a.locate_name = '" + locateName + "'");
                List<SysLocate> sysLocates = dbConn.Query<SysLocate>(stringBuilder.ToString()).ToList();
                return sysLocates;
            }
        }
        //根据分组号获取条码数量和
        public List<SysStock> GetStockQtyByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT SUM(qty) as qty FROM dbo.sys_stock (nolock) WHERE group_no = '" + groupNo + "' and status=40  and enabled='Y' ");
                List<SysStock> sysStocks1 = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks1;
            }
        }
        //根据分组号获取待入库的条码
        public List<SysStock> GetStockInfoByGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM dbo.sys_stock (nolock) WHERE group_no = '" + groupNo + "' and status=40  and enabled='Y' ");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }
        //根据库位查询所有在库的条码
        public List<SysStock> GetStockInfoByLocateName(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT * FROM sys_stock (nolock) WHERE locate_name='" + locateName + "' AND status in (50,60,80) AND enabled='Y'");
                List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysStocks;
            }
        }

		/// <summary>
		/// 根据扫描条码，判断是否有上级关系
		/// </summary>
		/// <param name="cartonNo"></param>
		/// <returns></returns>
		public List<WmsRackPackage> GetWmsRackPackageType(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilder = new StringBuilder();
				stringBuilder.Append("SELECT  * FROM dbo.wms_rack_package WHERE corton_no='" + cartonNo + "' AND enabled='Y' ");
				List<WmsRackPackage> packages = dbConn.Query<WmsRackPackage>(stringBuilder.ToString()).ToList();
				return packages;
			}
		}

		/// <summary>
		/// 根据周转箱条码找出包装条码
		/// </summary>
		/// <param name="cartonNo"></param>
		/// <returns></returns>
		public List<SysStock> GetWmsRackPackageByStock(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilderStock = new StringBuilder();
				stringBuilderStock.Append("SELECT  * FROM dbo.sys_stock WHERE carton_no in ( SELECT  corton_no FROM dbo.wms_rack_package WHERE rack_no='" + cartonNo + "' AND enabled='Y' ) AND enabled='Y' and Status=40 ");
				List<SysStock> stockInfos = dbConn.Query<SysStock>(stringBuilderStock.ToString()).ToList();
				return stockInfos;


			}
		}


		//根据分组号获取条码信息
		public List<SysStock> GetUpdateStockByGroupNo(string groupNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				StringBuilder stringBuilder = new StringBuilder();
				stringBuilder.Append("SELECT * FROM dbo.sys_stock (nolock) WHERE group_no = '" + groupNo + "' and status =40 and enabled='Y' ");
				List<SysStock> sysStocks = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
				return sysStocks;
			}
		}

		/// <summary>
		/// 根据包装条码找到周转箱
		/// </summary>
		/// <param name="cartonNo"></param>
		/// <returns></returns>
		public List<SysStock> GetWmsByStock(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilderStock = new StringBuilder();
				stringBuilderStock.Append(" SELECT * FROM dbo.sys_stock WHERE carton_no =(SELECT top 1 rack_no FROM dbo.wms_rack_package WHERE corton_no='" + cartonNo + "' AND enabled='Y' ) AND status=40 AND enabled='Y' ");
				List<SysStock> stockInfos = dbConn.Query<SysStock>(stringBuilderStock.ToString()).ToList();
				return stockInfos;


			}
		}

		List<WmsRackPackage_Extend> WmsRackPackages = new List<WmsRackPackage_Extend>();//定义全局


		/// <summary>
		/// 根据扫描的包装条码，查出所用的箱条码
		/// </summary>
		/// <param name="cartonNo"></param>
		/// <returns></returns>
		public List<WmsRackPackage_Extend> GetStockOrRackList(string cartonNo)
		{

			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilderRackPackage = new StringBuilder();
				stringBuilderRackPackage.Append("SELECT * FROM dbo.wms_rack_package WHERE rack_no ='" + cartonNo + "'  and enabled='Y' ");
				List<WmsRackPackage_Extend> WmsRackPackageCount = dbConn.Query<WmsRackPackage_Extend>(stringBuilderRackPackage.ToString()).ToList();

				WmsRackPackages = new List<WmsRackPackage_Extend>();
				foreach (var item in WmsRackPackageCount)
				{
					WmsRackPackages.Add(item);
					GetStockOrRackList2(item);
					GetStockOrRackList3(item);
				}
				return WmsRackPackages;

			}

		}
		/// <summary>
		/// 向下查询
		/// </summary>
		/// <param name="wmsRack"></param>
		public void GetStockOrRackList2(WmsRackPackage_Extend wmsRack)
		{

			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilderRackPackage = new StringBuilder();
				stringBuilderRackPackage.Append("SELECT * FROM dbo.wms_rack_package WHERE rack_no ='" + wmsRack.CortonNo + "'  and enabled='Y' ");
				List<WmsRackPackage_Extend> WmsRackPackageCount = dbConn.Query<WmsRackPackage_Extend>(stringBuilderRackPackage.ToString()).ToList();


				if (WmsRackPackageCount.Count != 0)
				{
					//wmsRack.package_Extends = new List<WmsRackPackage_Extend>();
					//wmsRack.package_Extends.AddRange(WmsRackPackageCount);

					foreach (var item in WmsRackPackageCount)
					{
						WmsRackPackages.Add(item);
						GetStockOrRackList2(item);
					}

				}

			}

		}

		/// <summary>
		/// 向上查询
		/// </summary>
		/// <param name="wmsRack"></param>
		public void GetStockOrRackList3(WmsRackPackage_Extend wmsRack)
		{

			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilderRackPackage = new StringBuilder();
				stringBuilderRackPackage.Append("SELECT * FROM dbo.wms_rack_package WHERE corton_no ='" + wmsRack.RackNo + "'  and enabled='Y' ");
				List<WmsRackPackage_Extend> WmsRackPackageCount = dbConn.Query<WmsRackPackage_Extend>(stringBuilderRackPackage.ToString()).ToList();


				if (WmsRackPackageCount.Count != 0)
				{
					//wmsRack.package_Extends = new List<WmsRackPackage_Extend>();
					//wmsRack.package_Extends.AddRange(WmsRackPackageCount);

					foreach (var item in WmsRackPackageCount)
					{
						WmsRackPackages.Add(item);
						GetStockOrRackList3(item);
					}

				}

			}

		}




		public List<SysStock> GetStockByStatusByCartonNo(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilder = new StringBuilder();
				stringBuilder.Append("SELECT a.* FROM sys_stock a (nolock)  WHERE  a.Status=40 and a.carton_no='" + cartonNo + "'");
				List<SysStock> sysStocksLocateCount = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
				return sysStocksLocateCount;
			}
		}

		/// <summary>
		/// 根据零件号，订单号来查询入库表明细数据
		/// </summary>
		/// <param name="cartonNo"></param>
		/// <param name="partId"></param>
		/// <returns></returns>
		public List<WmsOutstockDetail> GetOutstockDetailList(string cartonNo, string partId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				StringBuilder stringBuilder = new StringBuilder();
				stringBuilder.Append(" SELECT item_no,plan_date,plan_time FROM dbo.wms_outstock_detail WHERE order_no='" + cartonNo + "' AND part_id='" + partId + "' AND enabled='Y'");
				List<WmsOutstockDetail> outstockDetails = dbConn.Query<WmsOutstockDetail>(stringBuilder.ToString()).ToList();
				return outstockDetails;
			}
		}

		////根据分组号查询所有条码信息
		//public List<SysStock> GetStockInfoByInGroupNo(string groupNo)
		//{
		//    using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
		//    {
		//        dbConn.Open();
		//        StringBuilder stringBuilder = new StringBuilder();
		//        stringBuilder.Append("SELECT * FROM dbo.sys_stock WHERE group_no = '" + groupNo + "'  and status=50  and enabled='Y'");
		//        List<SysStock> sysstockByGroupNo = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
		//        return sysstockByGroupNo;
		//    }
		//}
		////根据条码获取库存在库信息
		//public List<SysStock> GetStockInfoByInCartonNo(string cartonNo)
		//{
		//    using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
		//    {
		//        dbConn.Open();
		//        StringBuilder stringBuilder = new StringBuilder();
		//        stringBuilder.Append("SELECT * FROM dbo.sys_stock WHERE carton_no = '" + cartonNo + "'  and status=50  and enabled='Y'");
		//        List<SysStock> sysstockByCartonNo = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
		//        return sysstockByCartonNo;
		//    }
		//}

		public List<SysStock> GetStockInfoByInGroupNo(string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT part_no,part_spec,sum(qty) qty,locate_name,count(1) carton_qty FROM dbo.sys_stock (nolock) WHERE group_no = '" + groupNo + "'  and status=50  and enabled='Y' group by part_no,part_spec,locate_name");
                List<SysStock> sysstockByGroupNo = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysstockByGroupNo;
            }
        }
        //根据条码获取库存在库信息
        public List<SysStock> GetStockInfoByInCartonNo(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT part_no,part_spec,sum(qty) qty,locate_name,count(1) carton_qty FROM dbo.sys_stock (nolock) WHERE carton_no = '" + cartonNo + "'  and status=50  and enabled='Y'  group by part_no,part_spec,locate_name");
                List<SysStock> sysstockByCartonNo = dbConn.Query<SysStock>(stringBuilder.ToString()).ToList();
                return sysstockByCartonNo;
            }
        }
        //事务批量执行添加、修改
        public int SQLTransaction(List<string> sqlStrings, List<DynamicParameters> parameterList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                if (dbConn.State == ConnectionState.Closed)
                {
                    dbConn.Open();
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();
                if (parameterList == null || parameterList.Count == 0)
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], null, transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
                else
                {
                    try
                    {
                        for (int i = 0; i < sqlStrings.Count; i++)
                        {
                            dbConn.Execute(sqlStrings[i], parameterList[i], transaction);
                        }
                        transaction.Commit();
                        return 1;
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
        }

    }
}
